﻿<%@ WebHandler Language="C#" Class="ContainerCurrentProgressDashBoard" %>

using System;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Collections;
using System.IO;

/**
 * Description: 零件进度表
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2023.7.28
 */

public class ContainerCurrentProgressDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    string IP = System.Configuration.ConfigurationManager.AppSettings["IP"];
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getContainerHistoryWorkflowData":
                        r = getContainerHistoryWorkflowData(jObject);//查询批次历史加工路线
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSelecteSpecInfo":
                        r = getSelecteSpecInfo(jObject);//查询所选工序基本信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWorkreportInfo":
                        r = getWorkreportInfo(jObject);//加工信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getShipInfo":
                        r = getShipInfo(jObject);//转工信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCommissionInfo":
                        r = getCommissionInfo(jObject);//外委信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getLossInfo":
                        r = getLossInfo(jObject);//报废信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFinishCheckInfo":
                        r = getFinishCheckInfo(jObject);//成品检验
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCheckPointResultInfo":
                        r = getCheckPointResultInfo(jObject);//检测项
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getStepInfo":
                        r = getStepInfo(jObject);//工步加工信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getUndoSpecInfo":
                        r = getUndoSpecInfo(jObject);//工序不加工
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getContainerMainInfo":
                        r = getContainerMainInfo(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getContainerSpecWorkReportData":
                        r = getContainerSpecWorkReportData(jObject);//批次生产加工信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    Result getData(JObject jObject)
    {
        Result r = new Result();
        string workflowid = "cci.workflowid";
        string sql = @"
SELECT f.factoryname,cci.containerid,cci.containername,cci.qty,cci.workflowstepid,cci.workflowstepname,cci.productname,cci.productrevision,
       cci.productdescription,cci.fromworkcentername,
       {1},
       CASE WHEN cci.containerstatus = 2 THEN 2 ELSE cci.isinstore END isinstore,
       NVL(cci.ishold,0) ishold,
       (SELECT MAX(ws.sequence) maxseq FROM workflowstep ws WHERE ws.workflowid = {0}) maxseq,
       cci.mfgordername,mfg.qty mfgqty,mfg.plannedstartdate,mfg.plannedcompletiondate,
       cci.workflowname,cci.workflowrevision,cci.familylistnames,NVL(od.orderstatusname,'打开') orderstatusname,
       DECODE(cci.containertype,'2','批量加工',3,'批量带子序号',7,'单价加工') containertype
FROM containercurrentinfo cci
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = cci.mfgorderid
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN containershiprecord csr ON csr.containershiprecordid = cci.shipid AND csr.toacceptor IS NOT NULL AND csr.isuse=0 
LEFT JOIN containershiprecive csre ON csre.containershiprecordid = csr.containershiprecordid AND csre.isuse=0
LEFT JOIN orderstatus od ON od.orderstatusid = mfg.orderstatusid
WHERE 1=1";
        //主制分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND ((cci.originalfactoryid = '{0}') OR (csr.tofactoryid='{0}' AND csre.reciver IS NULL))", jObject["factoryid"].ToString());
        }

        //主制工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
            //所选历史工艺路线
            if (jObject["workflowid"] != null && jObject["workflowid"].ToString() != "")
            {
                workflowid = "'" + jObject["workflowid"].ToString() + "'";
            }
        }

        //零件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname LIKE '%{0}%'", jObject["productname"].ToString());
        }

        //订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        // 开卡时间
        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            sql += string.Format(" AND c.originalstartdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
            sql += string.Format(" AND c.originalstartdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
        }

        //在制状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            string status = jObject["status"].ToString();
            if (status == "2")
            {
                sql += " AND cci.containerstatus = 2";
            }
            else
            {
                sql += string.Format(" AND cci.isinstore = '{0}'", jObject["status"].ToString());
            }
        }

        sql += @"
ORDER BY cci.containername";
        sql = string.Format(sql, workflowid, workflowid + " workflowid");
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dtContainer = dto.DBTable;
        if (dtContainer.Rows.Count == 0)
        {
            r = new Result();
            r.data = new DataTable();
            r.total = Convert.ToInt32(0);
            r.meta = new Result.Meta(200, "请求成功");
            return r;
        }
        DataTable dtResult = dtContainer.Copy();
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        string containeridList = "", workflowidList = "";
        foreach (DataRow row in dtResult.Rows)
        {
            containeridList += string.Format("'{0}',", row["containerid"].ToString());
            workflowidList += string.Format("'{0}',", row["workflowid"].ToString());
        }
        containeridList = string.Format("{0}", containeridList.TrimEnd(','));
        workflowidList = string.Format("{0}", workflowidList.TrimEnd(','));

        sql = @"SELECT MAX(ws.sequence) maxsequence FROM workflowstep ws WHERE ws.workflowid IN ({0})";
        sql = string.Format(sql, workflowidList);
        DataTable dtSelectedContainerMaxSeq = dal.GetDataTable(sql);
        //批次中最大工序数
        int maxWorkflowStepCount = Convert.ToInt32(dtSelectedContainerMaxSeq.Rows[0]["maxsequence"]);

        for (int i = 0; i < maxWorkflowStepCount; i++)
        {
            dtResult.Columns.Add("第" + (i + 1) + "序", typeof(object));
        }

        sql = @"
SELECT cci.containerid,cci.containername,cci.productname,ws.sequence,ws.workflowstepname,ws.workflowstepid, '第' || ws.sequence || '序' seq,
       cci.isinstore,cci.workflowstepname currentspec,cci.workflowstepid currentspecid,
       CASE WHEN csd.dispatchdate IS NULL AND csd.reportdate IS NULL THEN '未开始'
            --WHEN csd.dispatchdate IS NOT NULL AND csd.reportdate IS NULL THEN '已派工'
            WHEN csd.dispatchdate IS NOT NULL AND csd.recvdate IS NULL THEN '待领工'
            WHEN csd.recvdate IS NOT NULL AND csd.reportdate IS NULL THEN '待报工'
            WHEN csd.checkdate IS NULL THEN '已报工'
            WHEN csd.checkdate IS NOT NULL AND csd.usecompleted = 0 THEN '部分完工'
            WHEN csd.usecompleted = 1 THEN '已完工'
       END reportStatus,
       CASE WHEN cci.isinstore = -1 THEN '领料中'
            WHEN cci.isinstore = 1 THEN '完工入库'
            ELSE '在制'
       END instorestatus,
       CASE WHEN shipd.containershiprecordid IS NULL THEN ''
            WHEN pdr.parentid IS NOT NULL THEN '转工拒收'
            WHEN pdr.parentid IS NULL AND csr.senddate IS NULL THEN '转工待转出'
            WHEN pdr.parentid IS NULL AND csr.toacceptdate IS NULL THEN '转工待接收'
            WHEN pdr.parentid IS NULL AND csrr.completedate IS NULL THEN '转工待完成'
            WHEN pdr.parentid IS NULL AND csrr.completedate IS NOT NULL AND csrr.recivedate IS NULL THEN '转工待转回'
            WHEN pdr.parentid IS NULL AND csrr.recivedate IS NOT NULL THEN '转工已转回'
       END shipstatus,
       CASE WHEN ccsp.containerid IS NOT NULL THEN '检验待处理' ELSE '' END checkdealstatus,
       CASE WHEN cmm.containercommissioningid IS NULL THEN '' 
            WHEN cmm.outdate IS NOT NULL AND cmm.indate IS NULL THEN '外委已转出'
            WHEN cmm.indate IS NOT NULL THEN '外委已转回'
       END commissionstatus,
       CASE WHEN cni.containernonconforminginfoid IS NULL THEN '' 
            WHEN cni.checkemployeeid IS NULL THEN '待审理'
            WHEN cni.checkemployeeid IS NOT NULL THEN '有质量问题'
       END hasqualitystatus,
       CASE WHEN ws.wipmsglabel IS NOT NULL THEN '子工艺序' ELSE '' END issubworkflowstatus,
       CASE WHEN csno.containerstepnoshipid IS NULL THEN '' ELSE '工序不加工' END undostatus,
       CASE WHEN s.controlcode = 'LY05' THEN '外委工序' ELSE '' END iscommissionstepstatus,
       cci.workflowid,cci.workflowname,csd.dispatchid,
       CASE WHEN wf.factoryid <> s.factoryid THEN '转工序' ELSE '' END isshipstep
FROM containercurrentinfo cci
LEFT JOIN workflowstep ws ON ws.workflowid = {1}
LEFT JOIN (
     SELECT cd.recvdate,csw.checktype,csd.dispatchdate,csd.dispatchid,csd.reportdate,csd.checkdate,csd.usecompleted,csd.containerid,csd.workflowstepid,
        row_number() OVER(PARTITION BY csd.containerid,csd.workflowstepid ORDER BY csd.createdate DESC) seq 
     FROM containersteprecord csd
     LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = csd.containersteprecordid
     LEFT JOIN containerstepdispatch cd ON cd.containerstepdispatchid = csd.dispatchid AND cd.isuse = 0
     WHERE csd.containerid IN ({0})
) csd ON csd.containerid = cci.containerid AND csd.workflowstepid = ws.workflowstepid AND csd.seq = 1
LEFT JOIN (
    SELECT shipd.*
    FROM containershiprecordstep shipd
    INNER JOIN containershiprecord csr ON csr.containershiprecordid = shipd.containershiprecordid AND csr.isuse = 0
    WHERE shipd.containerid IN ({0})
) shipd ON shipd.containerid = cci.containerid AND shipd.workflowstepid = ws.workflowstepid
LEFT JOIN containershiprecord csr ON csr.containershiprecordid = shipd.containershiprecordid AND csr.isuse = 0
LEFT JOIN containershiprecive csrr ON csrr.containershiprecordid = csr.containershiprecordid AND csrr.isuse = 0
LEFT JOIN containerchecksuspending ccsp ON ccsp.containerid = cci.containerid AND ccsp.workflowstepid = ws.workflowstepid AND ccsp.isuse = 0 --检验待处理
LEFT JOIN containercommidetail cmmd ON cmmd.containerid = cci.containerid AND cmmd.workflowstepid = ws.workflowstepid AND cmmd.isuse = 0 --外委
LEFT JOIN containercommissioning cmm ON cmm.containercommissioningid = cmmd.containercommissioningid AND cmm.isuse = 0
LEFT JOIN containernonconforminginfo cni ON cni.containerid = cci.containerid AND cni.workflowstepid = ws.workflowstepid AND cni.isuse = 0 --不合格品审理
LEFT JOIN containerstepnoship csno ON csno.containerid = cci.containerid AND csno.workflowstepid = ws.workflowstepid AND csno.isuse = 0 AND csno.noship = 2
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN workflow wf ON wf.workflowid = ws.workflowid
LEFT JOIN processdealrecord pdr ON pdr.parentid = csr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE cci.containerid IN ({0})
ORDER BY cci.containerid,ws.sequence";
        sql = string.Format(sql, containeridList, workflowid);
        DataTable dtContainerSteps = dal.GetDataTable(sql);
        dtContainerSteps.Columns.Add("status");
        foreach (DataRow specRow in dtContainerSteps.Rows)
        {
            string currentspec = specRow["currentspec"].ToString();
            string workflowstepname = specRow["workflowstepname"].ToString();
            string productname = specRow["productname"].ToString();
            string currentWorkflowName = specRow["workflowname"].ToString();//批次当前工艺名称
            string currentWorkflowid = specRow["workflowid"].ToString();//批次当前工艺id

            if (workflowstepname.Contains(productname))
            {
                specRow["workflowstepname"] = workflowstepname.Replace(productname + "-", "");
            }
            if (currentspec.Contains(productname))
            {
                specRow["currentspec"] = currentspec.Replace(productname + "-", "");
            }

            string isinstore = specRow["isinstore"].ToString();//是否入库
            string issubworkflowstatus = specRow["issubworkflowstatus"].ToString();//是否子工艺序
            string undostatus = specRow["undostatus"].ToString();//工序不加工
            string commissionstatus = specRow["commissionstatus"].ToString();//外委
            string hasqualitystatus = specRow["hasqualitystatus"].ToString();//不合格品审理
            string checkdealstatus = specRow["checkdealstatus"].ToString();//检验待处理
            string reportStatus = specRow["reportStatus"].ToString();//加工信息
            string iscommissionstepstatus = specRow["iscommissionstepstatus"].ToString();//是否外委工序
            string dispatchid = specRow["dispatchid"].ToString();//派工id
            string isshipstep = specRow["isshipstep"].ToString();//是否转工序
            if (isinstore != "0")
            {
                if (jObject["workflowid"].ToString() == "" || jObject["workflowid"].ToString() == currentWorkflowid)
                {
                    specRow["status"] = specRow["instorestatus"].ToString();
                    continue;
                }
            }

            //子工艺序
            if (issubworkflowstatus != "")
            {
                if (specRow["shipstatus"].ToString() != "")
                {
                    specRow["status"] = specRow["shipstatus"].ToString();//有转工记录
                }
                else if (dispatchid != "")
                {
                    specRow["status"] = reportStatus;//转工序但有派工记录，按照普通工序判定状态
                }
                else
                {
                    specRow["status"] = "子工艺序";
                }
                continue;
            }

            //外委工序
            if (iscommissionstepstatus != "")
            {
                if (commissionstatus != "")
                {
                    specRow["status"] = commissionstatus;//有外委记录
                }
                else
                {
                    specRow["status"] = "外委工序";
                }
                continue;
            }

            //临时外委序
            if (commissionstatus != "")
            {
                specRow["status"] = commissionstatus;
                continue;
            }

            //工序不加工
            if (undostatus != "")
            {
                specRow["status"] = undostatus;
                continue;
            }

            //不合格品审理
            if (hasqualitystatus != "")
            {
                specRow["status"] = hasqualitystatus;
                continue;
            }

            //检验待处理
            if (checkdealstatus != "")
            {
                specRow["status"] = checkdealstatus;
                continue;
            }

            //转工序
            if (isshipstep != "" && specRow["shipstatus"].ToString() != "")
            {
                specRow["status"] = specRow["shipstatus"].ToString();
                continue;
            }

            specRow["status"] = reportStatus;
        }

        foreach (DataRow dr in dtResult.Rows)
        {
            for (int i = dtContainer.Columns.Count; i < dtResult.Columns.Count; i++)
            {
                string filter = string.Format("containerid = '{0}' AND seq='{1}'", dr["containerid"], dtResult.Columns[i].ColumnName);
                DataRow[] drList = dtContainerSteps.Select(filter);
                DataTable dtPlanSpec;
                if (drList.Length > 0)
                {
                    dtPlanSpec = drList.CopyToDataTable();
                    foreach (DataColumn col in dtPlanSpec.Columns)
                    {
                        col.ColumnName = col.ColumnName.ToLower();
                    }
                    dr[dtResult.Columns[i].ColumnName] = dtPlanSpec;
                }
                else
                {
                    dr[dtResult.Columns[i].ColumnName] = new DataTable();
                }
            }
        }

        r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询所选工序基本信息
    Result getSelecteSpecInfo(JObject jObject)
    {
        string sql = @"
SELECT c.containername,cci.productname,cci.productdescription,c.qty,
       s.specno,s.opdesc,
       ws.workflowstepname,
       cci.familylistnames,
       csrr.reportqty,csrr.passqty,csrr.lossqty,pt.producttypename,m.qty planqty,c.qty wipqty,
       csrr.teamname,cci.mfgordername,
       CASE WHEN cci.workflowname = 'FMW' THEN '完工入库' ELSE cci.workflowname END workflowname,
       cci.workflowrevision,csrr.defectqty,csrr.reworkqty
FROM  container c  
LEFT JOIN product p ON p.productid=c.productid
LEFT JOIN containercurrentinfo cci ON cci.containerid = c.containerid
LEFT JOIN producttype pt ON pt.producttypeid=p.producttypeid 
LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid 
LEFT JOIN (        
    SELECT SUM(csr.reportqty) reportqty,SUM(csr.passqty) passqty,
           SUM(csr.lossqty) lossqty,csr.containerid,csr.workflowstepid,csr.teamname,csr.defectqty,csr.reworkqty
    FROM containersteprecord csr 
    WHERE csr.containerid='{0}' 
          AND csr.workflowstepid='{1}' 
    GROUP BY csr.containerid,csr.workflowstepid,csr.teamname,csr.defectqty,csr.reworkqty
)  csrr ON csrr.containerid=c.containerid 
LEFT JOIN workflowstep ws ON ws.workflowstepid = '{1}'
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
WHERE c.containerid='{0}'";
        sql = string.Format(sql, jObject["containerid"].ToString(), jObject["workflowstepid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //批次历史加工路线
    Result getContainerHistoryWorkflowData(JObject jObject)
    {
        string sql = @"
SELECT t.workflowid,t.workflowname,t.workflowrevision,
       CASE WHEN t.workflowname = 'FMW' THEN '完工入库'
            ELSE t.workflowname || ':' || t.workflowrevision || '版工艺'
       END workflowinfo,
       t.currentworkflowid
FROM
(
  SELECT cci.workflowid,cci.workflowname,cci.workflowrevision,cci.workflowid currentworkflowid
  FROM containercurrentinfo cci
  WHERE 1=1 ";
        if (jObject["containerid"] != null && jObject["containerid"].ToString() != "")
        {
            sql += string.Format(" AND cci.containerid = '{0}'", jObject["containerid"].ToString());
        }
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }
        sql += @"
  UNION ALL
  SELECT csd.workflowid,csd.workflowname,csd.workflowrevision,cci.workflowid currentworkflowid
  FROM containersteprecord csd
  LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
  WHERE 1=1 ";
        if (jObject["containerid"] != null && jObject["containerid"].ToString() != "")
        {
            sql += string.Format(" AND csd.containerid = '{0}'", jObject["containerid"].ToString());
        }
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND csd.containername = '{0}'", jObject["containername"].ToString());
        }
        sql += @"
) t
GROUP BY t.workflowid,t.workflowname,t.workflowrevision,t.currentworkflowid
ORDER BY t.workflowid DESC";
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //工序加工记录信息
    Result getWorkreportInfo(JObject jObject)
    {
        string sql = @"
SELECT csd.containerstepdispatchid,csr.dispatchdate,csr.dispatchemployeename,csr.dispatchqty,csr.dispatchresourcename,
       rd.description resourcedescription,csr.reportdate,
       csr.reportemployeename,csr.reportqty,csr.teamname,
       DECODE(wcd.status, 0, '已派工','1','已接收','2','已报工','3','已完成','4','已关闭') AS ckpgdstatusname,
       CASE WHEN wrh.reporttype2 IS NULL THEN  DECODE(csw.checktype,1,'首件报工',3,'试件报工',4,'返修报工','其他报工') 
             ELSE DECODE(wrh.reporttype2,'0','首件','1','中件','2','末件','3','其他') 
       END checktypename,
       csr.checkdate,csr.checkemployeename,csr.passqty,csr.lossqty,csr.workflowrevision,csr.defectqty,csr.reworkqty
FROM containersteprecord csr
LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid=csr.containersteprecordid AND csw.isuse = 0
LEFT JOIN containerstepdispatch csd ON csd.containerstepdispatchid=csr.dispatchid AND csd.isuse = 0
LEFT JOIN workcenterdispatching wcd ON wcd.workcenterdispatchingid=csr.dispatchid
LEFT JOIN employee e ON e.employeeid=csd.workerid
LEFT JOIN workingreporthistory wrh ON wrh.workingreporthistoryid=csr.containersteprecordid
LEFT JOIN resourcedef rd ON rd.resourceid = csr.dispatchresourceid
WHERE csr.containerid='{0}' AND csr.workflowstepid='{1}'";
        sql = string.Format(sql, jObject["containerid"].ToString(), jObject["workflowstepid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        dtResult.Columns.Add("dispatchto");

        sql = @"
SELECT t.containerstepdispatchid,
       listagg(t.fullname,'、') WITHIN GROUP (ORDER BY t.fullname) dispatchto
FROM
(
  SELECT csd.containerstepdispatchid,
         NVL(e.fullname,e1.fullname) fullname
  FROM containersteprecord csr
  LEFT JOIN containerstepdispatch csd ON csd.containerstepdispatchid=csr.dispatchid and csd.isuse = 0 
  LEFT JOIN containerstepdispatchdetail csdd ON csdd.containerstepdispatchid=csd.containerstepdispatchid
  LEFT JOIN employee e ON e.employeeid=csdd.Workerid
  LEFT JOIN employee e1 ON e1.employeeid = csd.workerid
  WHERE csr.containerid='{0}' AND csr.workflowstepid='{1}'
  GROUP BY csd.containerstepdispatchid,e.fullname,e1.fullname
) t
GROUP BY t.containerstepdispatchid";
        sql = string.Format(sql, jObject["containerid"].ToString(), jObject["workflowstepid"].ToString());
        DataTable dtDispatchTo = dal.GetDataTable(sql);

        foreach (DataRow dr in dtResult.Rows)
        {
            string filter = string.Format("containerstepdispatchid = '{0}'", dr["containerstepdispatchid"].ToString());
            DataRow[] drs = dtDispatchTo.Select(filter);
            if (drs.Length > 0) { dr["dispatchto"] = drs[0]["dispatchto"].ToString(); }
        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //转工信息
    Result getShipInfo(JObject jObject)
    {
        string sql = @"
 SELECT e1.fullname sendoutepm,csr.senddate,e2.fullname returnemp,csre.recivedate returndate,
        ws1.workflowstepname outspec,
        ws2.workflowstepname inspec,
        e3.fullname proposername, csre.completedate,
        e4.fullname recivecompleter,e5.fullname toacceptor,csr.toacceptdate,csr.containerid,
        pdr.createdate denyDate,pdr.reasonnote,e6.fullname denyPerson     
 FROM containershiprecord csr 
 LEFT JOIN containershiprecive csre ON csre.containershiprecordid=csr.containershiprecordid AND csr.containerid=csre.containerid 
 LEFT JOIN employee e1 ON e1.employeeid=csr.sender--转工转出人
 LEFT JOIN employee e2 ON e2.employeeid=csre.reciver--转入接收人
 LEFT JOIN employee e3 ON e3.employeeid=csr.proposerid--转工申请人
 LEFT JOIN employee e4 ON e4.employeeid=csre.employeeid --承制完成人
 LEFT JOIN employee e5 ON e5.employeeid=csr.toacceptor --承制接收人
 LEFT JOIN workflowstep ws1 ON ws1.workflowstepid=csr.workflowstepid--转工开始工序
 LEFT JOIN workflowstep ws2 ON ws2.workflowstepid=csr.toworkflowstepid--转工结束工序
 LEFT JOIN workflowstep ws3 ON ws3.workflowid=ws1.workflowid AND (ws3.sequence>=ws1.sequence AND ws3.sequence<=ws2.sequence) 
 LEFT JOIN processdealrecord pdr ON pdr.parentid = csr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
 LEFT JOIN employee e6 ON e6.employeeid = pdr.employeeid --拒收人
 WHERE csr.isuse=0 AND csr.containerid='{0}' AND ws3.workflowstepid='{1}'";
        sql = string.Format(sql, jObject["containerid"].ToString(), jObject["workflowstepid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //外委信息
    Result getCommissionInfo(JObject jObject)
    {
        string sql = @"
SELECT ws1.workflowstepname,ws2.workflowstepname inworkflowstepname,cm.outdate,cm.indate,e1.fullname outname,e2.fullname inname,ct.customername,
       cm.outqty,cm.acceptanceqty,cm.lossqty
FROM containercommissioning cm
LEFT JOIN workflowstep ws1 ON ws1.workflowstepid=cm.workflowstepid
LEFT JOIN workflowstep ws2 ON ws2.workflowstepid=cm.inworkflowstepid
LEFT JOIN employee e1 ON e1.employeeid=cm.outemployeeid
LEFT JOIN employee e2 ON e2.employeeid=cm.inemployeeid
LEFT JOIN customer ct ON ct.customerid=cm.outcustomerid
LEFT JOIN workflowstep ws3 ON ws3.workflowid=ws1.workflowid AND (ws3.sequence>=ws1.sequence AND ws3.sequence<ws2.sequence) 
WHERE cm.containerid ='{0}' AND ws3.workflowstepid='{1}' AND cm.isuse= 0";
        sql = string.Format(sql, jObject["containerid"].ToString(), jObject["workflowstepid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //报废信息
    Result getLossInfo(JObject jObject)
    {
        string sql = @"
SELECT c.containername,qhd.qty scrapqty,e2.fullname fullname,l.lossreasonname,qhd.checkdate submitdate 
FROM qtyhistorydetails qhd
LEFT JOIN qtyhistory qh ON qh.qtyhistoryid=qhd.qtyhistoryid 
LEFT JOIN lossreason l ON l.lossreasonid=qhd.reasoncodeid 
LEFT JOIN historymainline hml ON hml.txnid=qhd.txnid  
LEFT JOIN employee e2 ON e2.employeeid=qhd.checkerid 
LEFT JOIN container c ON c.containerid=qhd.containerid 
WHERE c.containerid='{0}' AND qhd.changeqtytype=2 AND hml.workflowstepid='{1}' 
GROUP BY c.containername,qhd.qty,e2.fullname,l.lossreasonname,hml.txndate,qhd.checkdate,hml.txndate
UNION ALL
SELECT c.containername,ABS(ch.qty) scrapqty,e.fullname,l.lossreasonname,ch.createdate submitdate
FROM checkqtychangerecord ch
LEFT JOIN container c ON c.containerid=ch.containerid 
LEFT JOIN employee e ON e.employeeid=ch.employeeid
LEFT JOIN lossreason l ON l.lossreasonid=ch.reasonid
WHERE ch.changeqtytype=2 AND ch.isuse = 0 AND c.containerid='{0}' AND ch.workflowstepid='{1}' 
GROUP BY c.containername,ch.qty,e.fullname,l.lossreasonname,ch.createdate";
        sql = string.Format(sql, jObject["containerid"].ToString(), jObject["workflowstepid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //成品检验
    Result getFinishCheckInfo(JObject jObject)
    {
        string sql = @"
SELECT cfci.acceptanceqty,e.fullname,cfci.defectqty,cfci.reworkqty,cfci.lossqty,cfci.createdate 
FROM containerfinishedcheckinfo cfci 
LEFT JOIN employee e ON e.employeeid=cfci.checkerid 
WHERE cfci.containerid='{0}' AND cfci.workflowstepid='{1}'";
        sql = string.Format(sql, jObject["containerid"].ToString(), jObject["workflowstepid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //检测项
    Result getCheckPointResultInfo(JObject jObject)
    {
        string sql = @"
SELECT scp.name,scp.checkpointnotedis_b,scp.drawingrequiredis_b,DECODE(scp.checkinputtype, '1', '标准', '2', '临时', checkinputtype) checkinputtypename, 
       DECODE(scp.checktype, '1', '专用', '2', '共用', checktype) checktypename,DECODE(scp.keycheck, '1', '是', '0', '否', keycheck) keycheckname, 
       DECODE(scp.meopcheck, '1', '是', '0', '否', meopcheck) meopcheckname,DECODE(scp.qualitycheck, '1', '是', '0', '否', qualitycheck) qualitycheckname, 
       DECODE(scp.tccheckpointnote, NULL, '否', '是') istcm,e2.fullname workername,
       scpr.createdate,scpr.workcheckpointresultnotedis_b, e1.fullname checkername,scpr.checkdate,
       scpr.checkpointresultnote,c.containername,scpr.checkpointresultnotedis_b,scpr.workcheckpointresultnotedis
FROM stepcheckpointresult scpr 
INNER JOIN stepcheckpoint scp ON scp.stepcheckpointid=scpr.stepcheckpointid  
LEFT JOIN employee e1 ON e1.employeeid=scpr.checkerid 
LEFT JOIN employee e2 ON e2.employeeid=scpr.workerid  
LEFT JOIN container c ON c.containerid = scpr.childcontainerid 
WHERE scpr.containerid='{0}' AND scp.workflowstepid='{1}'";
        sql = string.Format(sql, jObject["containerid"].ToString(), jObject["workflowstepid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //工步加工信息
    Result getStepInfo(JObject jObject)
    {
        string sql = @"
SELECT si.stepno,si.description stepinfodesc,csd.createdate dispatchdate,e.fullname dispatchfullname,
       csd.qty dispatchqty,
       listagg(e2.fullname,'、') WITHIN GROUP (ORDER BY e2.fullname) dispatchworkname,
       csd.containerstepdispatchid,csw.createdate reportdate,
       rd.description resourcedesc,e3.fullname reportemployeename,t.teamname,csw.qty reportqty,cci.createdate checkdate,
       e4.fullname checkemployeename,cci.acceptanceqty2 passqty,cci.lossqty  
FROM containerstepdispatch2 csd
LEFT JOIN containerstepdispatchdetail2 csdd ON csdd.containerstepdispatchid=csd.containerstepdispatchid
LEFT JOIN employee e ON e.employeeid=csd.employeeid--派工人
LEFT JOIN employee e2 ON e2.employeeid=NVL(csd.workerid,csdd.workerid)--派给工人
LEFT JOIN stepinfo SI ON si.stepinfoid=csd.stepinfoid
LEFT JOIN resourcedef rd ON rd.resourceid=csd.resourceid
LEFT JOIN containerstepworkreport2 csw ON csw.containerstepdispatchid=csd.containerstepdispatchid AND csw.isuse=0
LEFT JOIN employee e3 ON e3.employeeid=csw.workerid
LEFT JOIN team t ON t.teamid=csw.teamid
LEFT JOIN containercheckinfo2 cci ON cci.containerstepworkreportid=csw.containerstepworkreportid AND cci.isuse=0
LEFT JOIN employee e4 ON e4.employeeid=cci.checkerid 
WHERE csd.isuse=0  AND csd.containerid='{0}' AND csd.workflowstepid='{1}' 
GROUP BY si.stepno,si.description ,csd.createdate ,e.fullname ,csd.qty,csd.containerstepdispatchid,csw.createdate,
         rd.description,e3.fullname,t.teamname,csw.qty,cci.createdate,e4.fullname,cci.acceptanceqty2,cci.lossqty,si.stepseq 
ORDER BY si.stepseq";
        sql = string.Format(sql, jObject["containerid"].ToString(), jObject["workflowstepid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //工序不加工信息
    Result getUndoSpecInfo(JObject jObject)
    {
        string sql = @"
SELECT cci.containername,ws.workflowstepname,em.fullname,
       DECODE(css.noship,2,'不进行','') noship,css.createdate
FROM containerstepnoship css
LEFT JOIN employee em ON em.employeeid = css.employeeid
LEFT JOIN containercurrentinfo cci ON cci.containerid = css.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = css.workflowstepid
WHERE css.containerid ='{0}' AND css.workflowstepid='{1}'";
        sql = string.Format(sql, jObject["containerid"].ToString(), jObject["workflowstepid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //批次主要信息
    Result getContainerMainInfo(JObject jObject)
    {
        string sql = @"
SELECT c.containername,pb.productname,p.description,pf.productfamilyname,pt.producttypename,m.qty planqty,c.qty wipqty,m.mfgordername,
       wc.workcentername||'/'||t.teamname workcenterandteam
FROM container c
LEFT JOIN product p ON p.productid=c.productid
LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
LEFT JOIN productfamily pf ON pf.productfamilyid=p.productfamilyid 
LEFT JOIN producttype pt ON pt.producttypeid=p.producttypeid
LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid
LEFT JOIN workcenter wc ON wc.workcenterid=c.workcenterid
LEFT JOIN team t ON t.teamid=c.teamid
WHERE c.containerid='{0}'";
        sql = string.Format(sql, jObject["containerid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //批次生产加工信息
    Result getContainerSpecWorkReportData(JObject jObject)
    {
        string sql = @"
SELECT csr.containername,csr.workflowstepname,csr.reportqty,csr.passqty eligibilityqty,cno.zbhgps qualityrecordqty, csr.lossqty scrapqty,
       em.fullname workername,csr.reportdate, 
       CASE WHEN (csr.usecompleted = 1 AND csw.containerfinishedcheckinfoid IS NULL AND csw.checktype = 1) THEN '首检' 
            WHEN (csr.usecompleted = 1 AND csw.containerfinishedcheckinfoid IS NULL) THEN '工序检' 
            WHEN(csr.usecompleted = 1 AND csw.containerfinishedcheckinfoid IS NOT NULL) THEN '成品检' 
            WHEN csr.usecompleted = 0 THEN '' END checktype 
FROM containersteprecord csr
LEFT JOIN employee em ON em.employeeid = csr.reportemployeeid 
LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = csr.containersteprecordid 
LEFT JOIN containernonconforminginfo cno ON cno.containerid=csr.containerid 
AND cno.workflowstepid=csr.workflowstepid AND cno.checkemployeeid IS NULL 
WHERE csr.containerid='{0}' 
ORDER BY csr.specno";
        sql = string.Format(sql, jObject["containerid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}